﻿ -- TMoviesGenres
DROP TABLE IF EXISTS TMoviesGenres;

CREATE TABLE TMoviesGenres (
genre_id 		INTEGER,
movie_id		INTEGER,
CONSTRAINT tmoviesgenres_pkey PRIMARY KEY (genre_id,movie_id)
);
GRANT ALL ON TABLE TMoviesGenres TO "watchMoviesModifiers";

CREATE OR REPLACE FUNCTION F_TMoviesGenres_Add_MovieGenre(TEXT,SMALLINT,"char",TEXT,TEXT) RETURNS VOID AS
	$$ 
	DECLARE
		id_genre integer;
		id_movie integer;
	BEGIN
		PERFORM F_TMovies_Add_Movie($1,$2,$3,$4);
		PERFORM F_TGenres_Add_Genre($5);
		id_genre := (SELECT genre_id from TGenres WHERE genre_name=$5);
		id_movie := (SELECT movie_id from TMovies WHERE movie_wmaz_code=$4);
		INSERT INTO TMoviesGenres (genre_id,movie_id) 
			SELECT id_genre,id_movie 
			WHERE NOT EXISTS ( SELECT genre_id,movie_id from TMoviesGenres as t WHERE t.genre_id = id_genre AND t.movie_id = id_movie);
	END;
	$$ 
	LANGUAGE plpgsql;
GRANT ALL ON FUNCTION F_TMoviesGenres_Add_MovieGenre(TEXT,SMALLINT,"char",TEXT,TEXT) TO "watchMoviesModifiers";
